package com.spark.prepareJob.report

import org.apache.log4j.{Level, Logger}
import org.apache.spark.SparkConf
import org.apache.spark.sql.SparkSession

/**
  * 多维度指标统计
  */
object MultiDimensionStatistic {
  def main(args: Array[String]): Unit = {
    Logger.getLogger("org.apache.hadoop").setLevel(Level.WARN)
    Logger.getLogger("org.apache.spark").setLevel(Level.WARN)
    Logger.getLogger("org.spark-project").setLevel(Level.WARN)

    val conf = new SparkConf().setAppName("MultiDimensionStatistic")
      .setMaster("local[*]")

    //val sparkSession = SparkSession.builder().config(conf).getOrCreate()
    val sparkSession = SparkSession.builder().appName("MultiDimensionStatistic").master("local[*]").getOrCreate()

    //通过sparkContext对象读取数据
    val adDataFrame = sparkSession.read.load("e:/out/standard/ad/")

    /* adDataFrame.printSchema()
     adDataFrame.show()*/
    adDataFrame.createOrReplaceTempView("ad_log")

    val sql =
      """
        |select 'area' type,
        |concat_ws("_",province,city) type_value,date,total_req,effective_req,ad_req,bid_num,win_num,concat_ws("",round(win_num/bid_num,2)*100,"%") win_rate,
        |show_num,click_num,concat_ws("",round(click_num/show_num,2)*100,"%") click_rate,
        |ad_cost,ad_consume
        |from
        |(select
        |provincename as province,
        |cityname as city,
        |reqdate as date,
        |sum(if(requestmode = 1 and processnode >=1 ,1,0)) as total_req,
        |sum(if(requestmode = 1 and processnode >=2 ,1,0)) as effective_req,
        |sum(if(requestmode = 1 and processnode = 3,1,0)) as ad_req,
        |sum(case
        |      when adplatformproviderid >=100000 and IsEffective = 1 and IsBilling =1 and isBid=1 and ADOrderID <> 1
        |      then 1
        |      else 0
        |end) bid_num,
        |sum(case
        |      when ADPlatformProviderID >=100000 and IsEffective = 1 and IsBilling = 1 and IsBilling =1
        |      then 1
        |      else 0
        |end ) win_num,
        |sum(case
        |     when RequestMode = 2 and IsEffective = 1 then 1
        |     else 0
        |end) show_num,
        |sum(case
        |     when RequestMode = 3 and IsEffective = 1 then 1
        |     else 0
        |end) click_num,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then WinPrice/1000
        |     else 0
        |end),2) ad_consume,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then adpayment/1000
        |     else 0
        |end),2) ad_cost
        |from ad_log
        |group by provincename,cityname,reqdate)a
        |union
        |select 'isp' type,
        |isp type_value,date,total_req,effective_req,ad_req,bid_num,win_num,concat_ws("",round(win_num/bid_num,2)*100,"%") win_rate,
        |show_num,click_num,concat_ws("",round(click_num/show_num,2)*100,"%") click_rate,
        |ad_cost,ad_consume
        |from
        |(select
        |ispname as isp,
        |reqdate as date,
        |sum(if(requestmode = 1 and processnode >=1 ,1,0)) as total_req,
        |sum(if(requestmode = 1 and processnode >=2 ,1,0)) as effective_req,
        |sum(if(requestmode = 1 and processnode = 3,1,0)) as ad_req,
        |sum(case
        |      when adplatformproviderid >=100000 and IsEffective = 1 and IsBilling =1 and isBid=1 and ADOrderID <> 1
        |      then 1
        |      else 0
        |end) bid_num,
        |sum(case
        |      when ADPlatformProviderID >=100000 and IsEffective = 1 and IsBilling = 1 and IsBilling =1
        |      then 1
        |      else 0
        |end ) win_num,
        |sum(case
        |     when RequestMode = 2 and IsEffective = 1 then 1
        |     else 0
        |end) show_num,
        |sum(case
        |     when RequestMode = 3 and IsEffective = 1 then 1
        |     else 0
        |end) click_num,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then WinPrice/1000
        |     else 0
        |end),2) ad_consume,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then adpayment/1000
        |     else 0
        |end),2) ad_cost
        |from ad_log
        |group by ispname,reqdate)a
        |union
        |select 'networkType' type,
        |network type_value,date,total_req,effective_req,ad_req,bid_num,win_num,concat_ws("",round(win_num/bid_num,2)*100,"%") win_rate,
        |show_num,click_num,concat_ws("",round(click_num/show_num,2)*100,"%") click_rate,
        |ad_cost,ad_consume
        |from
        |(select
        |networkmannername as network,
        |reqdate as date,
        |sum(if(requestmode = 1 and processnode >=1 ,1,0)) as total_req,
        |sum(if(requestmode = 1 and processnode >=2 ,1,0)) as effective_req,
        |sum(if(requestmode = 1 and processnode = 3,1,0)) as ad_req,
        |sum(case
        |      when adplatformproviderid >=100000 and IsEffective = 1 and IsBilling =1 and isBid=1 and ADOrderID <> 1
        |      then 1
        |      else 0
        |end) bid_num,
        |sum(case
        |      when ADPlatformProviderID >=100000 and IsEffective = 1 and IsBilling = 1 and IsBilling =1
        |      then 1
        |      else 0
        |end ) win_num,
        |sum(case
        |     when RequestMode = 2 and IsEffective = 1 then 1
        |     else 0
        |end) show_num,
        |sum(case
        |     when RequestMode = 3 and IsEffective = 1 then 1
        |     else 0
        |end) click_num,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then WinPrice/1000
        |     else 0
        |end),2) ad_consume,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then adpayment/1000
        |     else 0
        |end),2) ad_cost
        |from ad_log
        |group by networkmannername,reqdate)a
        |union
        |select 'devicetype' type,
        |devicetype,date,total_req,effective_req,ad_req,bid_num,win_num,concat_ws("",round(win_num/bid_num,2)*100,"%") win_rate,
        |show_num,click_num,concat_ws("",round(click_num/show_num,2)*100,"%") click_rate,
        |ad_cost,ad_consume
        |from
        |(select
        |devicetype,
        |reqdate as date,
        |sum(if(requestmode = 1 and processnode >=1 ,1,0)) as total_req,
        |sum(if(requestmode = 1 and processnode >=2 ,1,0)) as effective_req,
        |sum(if(requestmode = 1 and processnode = 3,1,0)) as ad_req,
        |sum(case
        |      when adplatformproviderid >=100000 and IsEffective = 1 and IsBilling =1 and isBid=1 and ADOrderID <> 1
        |      then 1
        |      else 0
        |end) bid_num,
        |sum(case
        |      when ADPlatformProviderID >=100000 and IsEffective = 1 and IsBilling = 1 and IsBilling =1
        |      then 1
        |      else 0
        |end ) win_num,
        |sum(case
        |     when RequestMode = 2 and IsEffective = 1 then 1
        |     else 0
        |end) show_num,
        |sum(case
        |     when RequestMode = 3 and IsEffective = 1 then 1
        |     else 0
        |end) click_num,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then WinPrice/1000
        |     else 0
        |end),2) ad_consume,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then adpayment/1000
        |     else 0
        |end),2) ad_cost
        |from ad_log
        |group by devicetype,reqdate)a
        |union
        |select 'client' type,
        |client,date,total_req,effective_req,ad_req,bid_num,win_num,concat_ws("",round(win_num/bid_num,2)*100,"%") win_rate,
        |show_num,click_num,concat_ws("",round(click_num/show_num,2)*100,"%") click_rate,
        |ad_cost,ad_consume
        |from
        |(select
        | client,
        |reqdate as date,
        |sum(if(requestmode = 1 and processnode >=1 ,1,0)) as total_req,
        |sum(if(requestmode = 1 and processnode >=2 ,1,0)) as effective_req,
        |sum(if(requestmode = 1 and processnode = 3,1,0)) as ad_req,
        |sum(case
        |      when adplatformproviderid >=100000 and IsEffective = 1 and IsBilling =1 and isBid=1 and ADOrderID <> 1
        |      then 1
        |      else 0
        |end) bid_num,
        |sum(case
        |      when ADPlatformProviderID >=100000 and IsEffective = 1 and IsBilling = 1 and IsBilling =1
        |      then 1
        |      else 0
        |end ) win_num,
        |sum(case
        |     when RequestMode = 2 and IsEffective = 1 then 1
        |     else 0
        |end) show_num,
        |sum(case
        |     when RequestMode = 3 and IsEffective = 1 then 1
        |     else 0
        |end) click_num,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then WinPrice/1000
        |     else 0
        |end),2) ad_consume,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then adpayment/1000
        |     else 0
        |end),2) ad_cost
        |from ad_log
        |group by client,reqdate) a
        |union
        |select 'mediatype' type,
        |mediatype,date,total_req,effective_req,ad_req,bid_num,win_num,concat_ws("",round(win_num/bid_num,2)*100,"%") win_rate,
        |show_num,click_num,concat_ws("",round(click_num/show_num,2)*100,"%") click_rate,
        |ad_cost,ad_consume
        |from
        |(select
        |mediatype,
        |reqdate as date,
        |sum(if(requestmode = 1 and processnode >=1 ,1,0)) as total_req,
        |sum(if(requestmode = 1 and processnode >=2 ,1,0)) as effective_req,
        |sum(if(requestmode = 1 and processnode = 3,1,0)) as ad_req,
        |sum(case
        |      when adplatformproviderid >=100000 and IsEffective = 1 and IsBilling =1 and isBid=1 and ADOrderID <> 1
        |      then 1
        |      else 0
        |end) bid_num,
        |sum(case
        |      when ADPlatformProviderID >=100000 and IsEffective = 1 and IsBilling = 1 and IsBilling =1
        |      then 1
        |      else 0
        |end ) win_num,
        |sum(case
        |     when RequestMode = 2 and IsEffective = 1 then 1
        |     else 0
        |end) show_num,
        |sum(case
        |     when RequestMode = 3 and IsEffective = 1 then 1
        |     else 0
        |end) click_num,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then WinPrice/1000
        |     else 0
        |end),2) ad_consume,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then adpayment/1000
        |     else 0
        |end),2) ad_cost
        |from ad_log
        |group by mediatype,reqdate) a
        |union
        |select 'channelid' type,
        |channel,date,total_req,effective_req,ad_req,bid_num,win_num,concat_ws("",round(win_num/bid_num,2)*100,"%") win_rate,
        |show_num,click_num,concat_ws("",round(click_num/show_num,2)*100,"%") click_rate,
        |ad_cost,ad_consume
        |from
        |(select
        |channelid channel,
        |reqdate as date,
        |sum(if(requestmode = 1 and processnode >=1 ,1,0)) as total_req,
        |sum(if(requestmode = 1 and processnode >=2 ,1,0)) as effective_req,
        |sum(if(requestmode = 1 and processnode = 3,1,0)) as ad_req,
        |sum(case
        |      when adplatformproviderid >=100000 and IsEffective = 1 and IsBilling =1 and isBid=1 and ADOrderID <> 1
        |      then 1
        |      else 0
        |end) bid_num,
        |sum(case
        |      when ADPlatformProviderID >=100000 and IsEffective = 1 and IsBilling = 1 and IsBilling =1
        |      then 1
        |      else 0
        |end ) win_num,
        |sum(case
        |     when RequestMode = 2 and IsEffective = 1 then 1
        |     else 0
        |end) show_num,
        |sum(case
        |     when RequestMode = 3 and IsEffective = 1 then 1
        |     else 0
        |end) click_num,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then WinPrice/1000
        |     else 0
        |end),2) ad_consume,
        |round(sum(case
        |     when ADPlatformProviderID >= 100000 and IsEffective = 1 and IsBilling = 1 and IsWin = 1 and ADOrderID >= 200000 and ADCreativeID >= 200000
        |     then adpayment/1000
        |     else 0
        |end),2) ad_cost
        |from ad_log
        |group by channelid,reqdate) a
      """.stripMargin

    sparkSession.sql(sql).show()
    sparkSession.stop()
  }
}
